
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/14/2014 22:05:19
-- Generated from EDMX file: C:\Users\Bruno\Documents\projeto-final-2011-2\Model\Entity.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ProjetoEstimativas];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ProjectEpic]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Epic] DROP CONSTRAINT [FK_ProjectEpic];
GO
IF OBJECT_ID(N'[dbo].[FK_ReleaseProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Release] DROP CONSTRAINT [FK_ReleaseProject];
GO
IF OBJECT_ID(N'[dbo].[FK_ReleaseEpic]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Epic] DROP CONSTRAINT [FK_ReleaseEpic];
GO
IF OBJECT_ID(N'[dbo].[FK_EpicHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[History] DROP CONSTRAINT [FK_EpicHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_HistoryTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_HistoryTask];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[History] DROP CONSTRAINT [FK_ProjectHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_ReleaseSprint]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sprint] DROP CONSTRAINT [FK_ReleaseSprint];
GO
IF OBJECT_ID(N'[dbo].[FK_SprintHistory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[History] DROP CONSTRAINT [FK_SprintHistory];
GO
IF OBJECT_ID(N'[dbo].[FK_SprintTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_SprintTask];
GO
IF OBJECT_ID(N'[dbo].[FK_PerfilUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_PerfilUser];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectUserProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectUser] DROP CONSTRAINT [FK_ProjectUserProject];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectUserUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectUser] DROP CONSTRAINT [FK_ProjectUserUser];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_ProjectTask];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[User];
GO
IF OBJECT_ID(N'[dbo].[Project]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Project];
GO
IF OBJECT_ID(N'[dbo].[Epic]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Epic];
GO
IF OBJECT_ID(N'[dbo].[Task]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Task];
GO
IF OBJECT_ID(N'[dbo].[History]', 'U') IS NOT NULL
    DROP TABLE [dbo].[History];
GO
IF OBJECT_ID(N'[dbo].[Release]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Release];
GO
IF OBJECT_ID(N'[dbo].[Sprint]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sprint];
GO
IF OBJECT_ID(N'[dbo].[Perfil]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Perfil];
GO
IF OBJECT_ID(N'[dbo].[ProjectUser]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectUser];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'User'
CREATE TABLE [dbo].[User] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [Senha] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NULL,
    [qtdHoras] int  NOT NULL,
    [Perfil_Id] int  NOT NULL
);
GO

-- Creating table 'Project'
CREATE TABLE [dbo].[Project] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [DataInicio] datetime  NOT NULL,
    [DataFim] datetime  NOT NULL,
    [Descricao] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Epic'
CREATE TABLE [dbo].[Epic] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Descricao] nvarchar(max)  NOT NULL,
    [MelhorCaso] float  NOT NULL,
    [CasoMedio] float  NOT NULL,
    [PiorCaso] float  NOT NULL,
    [Project_Id] int  NOT NULL,
    [Release_Id] int  NULL
);
GO

-- Creating table 'Task'
CREATE TABLE [dbo].[Task] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Descricao] nvarchar(max)  NOT NULL,
    [MelhorCaso] float  NOT NULL,
    [CasoMedio] float  NOT NULL,
    [PiorCaso] float  NOT NULL,
    [ProjectId] int  NOT NULL,
    [History_Id] int  NULL,
    [Sprint_Id] int  NULL
);
GO

-- Creating table 'History'
CREATE TABLE [dbo].[History] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Descricao] nvarchar(max)  NOT NULL,
    [MelhorCaso] float  NOT NULL,
    [CasoMedio] float  NOT NULL,
    [PiorCaso] float  NOT NULL,
    [Epic_Id] int  NULL,
    [Project_Id] int  NOT NULL,
    [Sprint_Id] int  NULL
);
GO

-- Creating table 'Release'
CREATE TABLE [dbo].[Release] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DataInicio] datetime  NULL,
    [DataFim] datetime  NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Project_Id] int  NOT NULL
);
GO

-- Creating table 'Sprint'
CREATE TABLE [dbo].[Sprint] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [DataInicio] datetime  NOT NULL,
    [DataFim] datetime  NOT NULL,
    [Release_Id] int  NOT NULL
);
GO

-- Creating table 'Perfil'
CREATE TABLE [dbo].[Perfil] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Nome] nvarchar(max)  NOT NULL,
    [Nivel] int  NOT NULL
);
GO

-- Creating table 'ProjectUser'
CREATE TABLE [dbo].[ProjectUser] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProjectId] int  NOT NULL,
    [UserId] int  NOT NULL,
    [QtdHoras] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [PK_User]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Project'
ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [PK_Project]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Epic'
ALTER TABLE [dbo].[Epic]
ADD CONSTRAINT [PK_Epic]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [PK_Task]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'History'
ALTER TABLE [dbo].[History]
ADD CONSTRAINT [PK_History]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Release'
ALTER TABLE [dbo].[Release]
ADD CONSTRAINT [PK_Release]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Sprint'
ALTER TABLE [dbo].[Sprint]
ADD CONSTRAINT [PK_Sprint]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Perfil'
ALTER TABLE [dbo].[Perfil]
ADD CONSTRAINT [PK_Perfil]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProjectUser'
ALTER TABLE [dbo].[ProjectUser]
ADD CONSTRAINT [PK_ProjectUser]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Project_Id] in table 'Epic'
ALTER TABLE [dbo].[Epic]
ADD CONSTRAINT [FK_ProjectEpic]
    FOREIGN KEY ([Project_Id])
    REFERENCES [dbo].[Project]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectEpic'
CREATE INDEX [IX_FK_ProjectEpic]
ON [dbo].[Epic]
    ([Project_Id]);
GO

-- Creating foreign key on [Project_Id] in table 'Release'
ALTER TABLE [dbo].[Release]
ADD CONSTRAINT [FK_ReleaseProject]
    FOREIGN KEY ([Project_Id])
    REFERENCES [dbo].[Project]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReleaseProject'
CREATE INDEX [IX_FK_ReleaseProject]
ON [dbo].[Release]
    ([Project_Id]);
GO

-- Creating foreign key on [Release_Id] in table 'Epic'
ALTER TABLE [dbo].[Epic]
ADD CONSTRAINT [FK_ReleaseEpic]
    FOREIGN KEY ([Release_Id])
    REFERENCES [dbo].[Release]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReleaseEpic'
CREATE INDEX [IX_FK_ReleaseEpic]
ON [dbo].[Epic]
    ([Release_Id]);
GO

-- Creating foreign key on [Epic_Id] in table 'History'
ALTER TABLE [dbo].[History]
ADD CONSTRAINT [FK_EpicHistory]
    FOREIGN KEY ([Epic_Id])
    REFERENCES [dbo].[Epic]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EpicHistory'
CREATE INDEX [IX_FK_EpicHistory]
ON [dbo].[History]
    ([Epic_Id]);
GO

-- Creating foreign key on [History_Id] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_HistoryTask]
    FOREIGN KEY ([History_Id])
    REFERENCES [dbo].[History]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HistoryTask'
CREATE INDEX [IX_FK_HistoryTask]
ON [dbo].[Task]
    ([History_Id]);
GO

-- Creating foreign key on [Project_Id] in table 'History'
ALTER TABLE [dbo].[History]
ADD CONSTRAINT [FK_ProjectHistory]
    FOREIGN KEY ([Project_Id])
    REFERENCES [dbo].[Project]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectHistory'
CREATE INDEX [IX_FK_ProjectHistory]
ON [dbo].[History]
    ([Project_Id]);
GO

-- Creating foreign key on [Release_Id] in table 'Sprint'
ALTER TABLE [dbo].[Sprint]
ADD CONSTRAINT [FK_ReleaseSprint]
    FOREIGN KEY ([Release_Id])
    REFERENCES [dbo].[Release]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReleaseSprint'
CREATE INDEX [IX_FK_ReleaseSprint]
ON [dbo].[Sprint]
    ([Release_Id]);
GO

-- Creating foreign key on [Sprint_Id] in table 'History'
ALTER TABLE [dbo].[History]
ADD CONSTRAINT [FK_SprintHistory]
    FOREIGN KEY ([Sprint_Id])
    REFERENCES [dbo].[Sprint]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SprintHistory'
CREATE INDEX [IX_FK_SprintHistory]
ON [dbo].[History]
    ([Sprint_Id]);
GO

-- Creating foreign key on [Sprint_Id] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_SprintTask]
    FOREIGN KEY ([Sprint_Id])
    REFERENCES [dbo].[Sprint]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SprintTask'
CREATE INDEX [IX_FK_SprintTask]
ON [dbo].[Task]
    ([Sprint_Id]);
GO

-- Creating foreign key on [Perfil_Id] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [FK_PerfilUser]
    FOREIGN KEY ([Perfil_Id])
    REFERENCES [dbo].[Perfil]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PerfilUser'
CREATE INDEX [IX_FK_PerfilUser]
ON [dbo].[User]
    ([Perfil_Id]);
GO

-- Creating foreign key on [ProjectId] in table 'ProjectUser'
ALTER TABLE [dbo].[ProjectUser]
ADD CONSTRAINT [FK_ProjectUserProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[Project]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectUserProject'
CREATE INDEX [IX_FK_ProjectUserProject]
ON [dbo].[ProjectUser]
    ([ProjectId]);
GO

-- Creating foreign key on [UserId] in table 'ProjectUser'
ALTER TABLE [dbo].[ProjectUser]
ADD CONSTRAINT [FK_ProjectUserUser]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[User]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectUserUser'
CREATE INDEX [IX_FK_ProjectUserUser]
ON [dbo].[ProjectUser]
    ([UserId]);
GO

-- Creating foreign key on [ProjectId] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [FK_ProjectTask]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[Project]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectTask'
CREATE INDEX [IX_FK_ProjectTask]
ON [dbo].[Task]
    ([ProjectId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------